package matlap.database.jdbc;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import matlap.database.ConnectionManager;
import matlap.database.dao.ArticleDAO;
import matlap.shared.Article;
import matlap.shared.Author;
import matlap.shared.Topic;
import matlap.shared.exceptions.MyException;

public class ArticleJdbcDAO implements ArticleDAO {
	Connection con = null;
	private Statement st;
	private ResultSet rs;

	/**
	 * Turns back all of the authors name in Author list.
	 */

	public List<Author> selectAuthorName() throws MyException{

		List<Author> list = new ArrayList<Author>();
		String ss;
		String query = "SELECT author_name FROM authors ORDER BY author_name";

		
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			st = con.createStatement();
		    rs = st.executeQuery(query);

			int i = 0;
			while (rs.next()) {
				ss = rs.getString("author_name");
				Author aut = new Author(0, 0, ss);
				list.add(i, aut);
				i++;
			}
			
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	/**
	 * Turns back all of the topics name in Topic list.
	 */

	public List<Topic> selectTopicsName() {

		List<Topic> list = new ArrayList<Topic>();
		String ss;
		String query = "SELECT topics_name FROM topics ORDER BY topics_name";

		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(query);

			int i = 0;
			while (rs.next()) {
				ss = rs.getString("topics_name");
				Topic top = new Topic(0, ss);
				list.add(i, top);
				i++;
			}
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	/**
	 * Turns back the articles title, author name, topic name AND article text
	 * in Article list, by adding article title, topic name AND author name.
	 */
	public List<Article> selectArticle(String articleName, String topicsName,
			String authorName) {

		List<Article> list = new ArrayList<Article>();
		String address = "";
		int authorID = 0;
		int topicsID = 0;
		String articleText = "";
		String title = "";
		String author = "";
		String topic = "";
		String query = "SELECT * FROM  articles,authors,topics WHERE articles.authorID=authors.authorID AND articles.topicsID=topics.topicsID ORDER BY author_name, address";

		if (articleName.length() == 0) {
			if (topicsName.length() == 0) {
				if (authorName.length() == 0) {

				} else {
					query = "SELECT * FROM articles,authors,topics WHERE articles.authorID=authors.authorID AND articles.topicsID=topics.topicsID AND author_name = '"
							+ authorName + "' ORDER BY author_name, address";
				}
			} else {
				if (authorName.length() == 0) {
					query = "SELECT * FROM articles,authors,topics WHERE articles.authorID=authors.authorID AND articles.topicsID=topics.topicsID AND topics_name = '"
							+ topicsName + "' ORDER BY author_name, address";
				} else {
					query = "SELECT * FROM articles,authors,topics WHERE articles.authorID=authors.authorID AND articles.topicsID=topics.topicsID AND author_name = '"
							+ authorName
							+ "' AND topics_name= '"
							+ topicsName
							+ "' ORDER BY author_name, address";
				}
			}

		} else {
			if (topicsName.length() == 0) {
				if (authorName.length() == 0) {
					query = "SELECT * FROM  articles,authors,topics WHERE articles.authorID=authors.authorID AND articles.topicsID=topics.topicsID AND address like '%"
							+ articleName + "%' ORDER BY author_name, address";
				} else {
					query = "SELECT * FROM  articles,authors,topics WHERE articles.authorID=authors.authorID AND articles.topicsID=topics.topicsID AND author_name = '"
							+ authorName
							+ "' AND address like '%"
							+ articleName + "%' ORDER BY author_name, address";
				}
			} else {
				if (authorName.length() == 0) {
					query = "SELECT * FROM  articles,authors,topics WHERE articles.authorID=authors.authorID AND articles.topicsID=topics.topicsID AND topics_name = '"
							+ topicsName
							+ "' AND address like '%"
							+ articleName + "%' ORDER BY author_name, address";
				} else {
					query = "SELECT * FROM articles,authors,topics WHERE articles.authorID=authors.authorID AND articles.topicsID=topics.topicsID AND author_name = '"
							+ authorName
							+ "' AND topics_name= '"
							+ topicsName
							+ "' AND address like '%" + articleName + "%' ORDER BY author_name, address";
				}
			}
		}

		InputStream in = null;

		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(query);

			int i = 0;

			while (rs.next()) {
				address = rs.getString("author_name") + ":"
						+ rs.getString("address") + "("
						+ rs.getString("topics_name") + ")";
				authorID = rs.getInt("authorID");
				topicsID = rs.getInt("topicsID");
				articleText = rs.getString("article_text");
				title = rs.getString("address");
				author = rs.getString("author_name");
				topic = rs.getString("topics_name");

				int leng = articleText.length();
				byte[] b = new byte[leng];
				in = rs.getBinaryStream("article_text");
				try {
					in.read(b, 0, leng);
					OutputStream out = new FileOutputStream("pdf" + i + ".pdf");
					out.write(b, 0, leng);
					out.close();

					articleText = "pdf" + i + ".pdf";

					Article art = new Article(0, address, authorID, topicsID,
							articleText, title, author, topic);
					list.add(i, art);
					i++;

				} catch (FileNotFoundException e) {
					e.printStackTrace();
				} catch (IOException e) {
					e.printStackTrace();
				}
				
			}
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return list;
	}

	/**
	 * Inserts an article to the article table by adding article title, author
	 * name, topic name AND file name.
	 */
	public boolean insertArticle(String address, String authorName,
			String topicsName, String fileName, String path) throws MyException {
		boolean ok = false;
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		int authorID = selectAuthorID(authorName);
		int topicsID = selectTopicsID(topicsName);
		int number = getNumber(authorName, address, topicsName);
		if (number != 0)
			return false;
		String s = new String("INSERT INTO articles VALUES (?,?,?,?,?)");

		PreparedStatement ps = null;
		FileInputStream fis = null;
		try {
			String f = "";
			if (path.charAt(path.length() - 1) == '\\') {
				f = path + fileName;
			} else {
				f = path + "\\" + fileName;
			}
			File file = new File(f);
			fis = new FileInputStream(f);

			ps = con.prepareStatement(s);

			ps.setString(1, null);
			ps.setString(2, address);
			ps.setInt(3, authorID);
			ps.setInt(4, topicsID);
			ps.setAsciiStream(5, fis, (int) file.length());

			ps.executeUpdate();
			ok = true;

		} catch (SQLException ex) {
			ok=true;
			throw new MyException("Nem sikerült a feltöltés. Ellenőrizd az adatok helyességét!");
		} catch (FileNotFoundException e) {
			ok=true;
			throw new MyException("Nem sikerült a feltöltés. Ellenőrizd az adatok helyességét!");
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
			}
		}

		return ok;
	}

	public void insertAuthor(String authorName, String localityName) {
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		int localityID = selectLocalityID(localityName);
		String s = new String("INSERT INTO authors VALUES (?,?,?)");

		PreparedStatement ps = null;
		try {
			ps = con.prepareStatement(s);

			ps.setString(1, null);
			ps.setInt(2, localityID);
			ps.setString(3, authorName);

			ps.executeUpdate();

		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	private int selectLocalityID(String localityName) {
		int ID = 0;
		String query = "SELECT localityID FROM locality WHERE locality_name = '"
				+ localityName + "'";
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			st = con.createStatement();
		    rs = st.executeQuery(query);

			while (rs.next()) {
				ID = rs.getInt("localityID");
			}
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		return ID;
	}

	/**
	 * Turns back the authorID (int) FROM the authors table by adding author
	 * name (String).
	 */
	private int selectAuthorID(String authorName) {
		int ID = 0;
		String query = "SELECT authorID FROM authors WHERE author_name = '"
				+ authorName + "'";
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			st = con.createStatement();
		    rs = st.executeQuery(query);

			while (rs.next()) {
				ID = rs.getInt("authorID");
			}
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		return ID;
	}

	/**
	 * Turns back the topicID (int) FROM the topics table by adding topic name
	 * (String).
	 */
	private int selectTopicsID(String topicsName) {
		int ID = 0;
		String query = "SELECT topicsID FROM topics WHERE topics_name = '"
				+ topicsName + "'";
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			st = con.createStatement();
		    rs = st.executeQuery(query);

			while (rs.next()) {
				ID = rs.getInt("topicsID");
			}
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		return ID;
	}

	public void insertTopic(String name) {
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			st = con.createStatement();

			String s = new String("INSERT INTO topics VALUES(" + null + ",'"
					+ name + "')");
			st.executeUpdate(s);

		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

	private int getNumber(String author, String title, String topic) {
		int nr = 0;
		String query = "SELECT COUNT(articleID) as darab "
				+ " FROM articles,authors,topics "
				+ ""
				+ "WHERE articles.authorID=authors.authorID AND articles.topicsID=topics.topicsID AND author_name='"
				+ author + "' AND " + " topics_name='" + topic
				+ "' AND address='" + title + "'";
		try {
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(query);
			while (rs.next()) {
				nr = Integer.parseInt(rs.getString("darab"));
			}
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}

		return nr;
	}

	public int getArticlesNumber() {
		int nr = 0;
		String query = "SELECT COUNT(articleID) AS darab FROM articles";
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(query);
			while (rs.next()) {
				nr = Integer.parseInt(rs.getString("darab"));
			}
		} catch (SQLException ex) {
			System.err.println("hiba DistributorsServiceImpl");
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return nr;
	}

}
